package org.liulinlin.sejw.price;

import org.liulinlin.sejw.db.*;


import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import java.util.Random;

import org.osoa.sca.annotations.Destroy;
import org.osoa.sca.annotations.Init;
import org.osoa.sca.annotations.Service;
//import org.osoa.sca.annotations.Init;

@Service(QueryAnswerPriceService.class)
public class QueryAnswerPriceServiceImpl implements QueryAnswerPriceService {
	private DBConnectionManager connMgr;
	
	@Init
	public void init() {
		connMgr = DBConnectionManager.getInstance();
	}
	
	@Destroy
	public void destroy() {
		connMgr.release();
	}
	
	/**
	 * 得到所有询价单
	 */
	public List<QueryPriceForm> getQueryPriceForm() {
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return null;
		}
		
		List<QueryPriceForm> res = new ArrayList<QueryPriceForm>();
		
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select * from PriceQueryForm");
		
			while (rs.next()) {
				QueryPriceForm item = new QueryPriceForm(rs.getInt("PQF_ID"));								
				res.add(item);
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			System.out.println("Exception in QueryAnswerPriceServiceImpl.getQueryPriceForm().");
			e.printStackTrace();
		}
		
		connMgr.freeConnection("shipping_export", con);
		
		return res;
	}
	/**
	 * 得到所有报价单
	 */
	public List<AnswerPriceForm> getAnswerPriceForm() {
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return null;
		}
		
		List<AnswerPriceForm> res = new ArrayList<AnswerPriceForm>();
		
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select * from AnswerPriceForm");
		
			while (rs.next()) {
				AnswerPriceForm item = new AnswerPriceForm(rs.getInt("APF_ID"));				
				res.add(item);
			}
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			System.out.println("Exception in QueryAnswerPriceServiceImpl.getAnswerPriceForm().");
			e.printStackTrace();
		}
		
		connMgr.freeConnection("shipping_export", con);
		
		return res;
	}
	/**
	 * 对某个特定的询价单报价
	 */
	public AnswerPriceForm answerPrice(QueryPriceForm queryPriceForm) {
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return null;
		}
		
		AnswerPriceForm res = new AnswerPriceForm();
		ResultSet rs = null;
		
		res.setId(queryPriceForm.getID());
		
		//设置该报价单对应的询价单
		res.setQueryPriceForm(queryPriceForm);
		
		//计算海运费用
		double shippingFee = 0;
		try {
			Statement stmt = con.createStatement();
			rs = stmt.executeQuery("select * from FeeItem where Fee_Name = '海运费'");
		
			rs.next();
			shippingFee = rs.getDouble("Fee_Std");
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		res.setShippingCost(getPortDistance(queryPriceForm.getStart(), 
				queryPriceForm.getEnd()) * shippingFee);
		
		//计算陆运费
		if (queryPriceForm.getLandTransport() == true) {
			try {
				Statement stmt = con.createStatement();
				rs = stmt.executeQuery("select * from FeeItem where Fee_Name = '陆运费'");
			
				rs.next();
				res.setLandCost(getLandDistance(queryPriceForm.getStart(),
						queryPriceForm.getLoc()) * rs.getDouble("Fee_Std"));
				
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		//计算报关费
		if (queryPriceForm.getDeclare() == true) {
			try {
				Statement stmt = con.createStatement();
				rs = stmt.executeQuery("select * from FeeItem where Fee_Name = '报关费'");
			
				rs.next();
				res.setDeclareCost(rs.getDouble("Fee_Std"));
				
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		//计算订舱费
		if (queryPriceForm.getOrder() == true) {
			try {
				Statement stmt = con.createStatement();
				rs = stmt.executeQuery("select * from FeeItem where Fee_Name = '订舱费'");
			
				rs.next();
				res.setOrderCost(rs.getDouble("Fee_Std"));
				
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		connMgr.freeConnection("shipping_export", con);
		
		return res;
	}
	/**
	 * 向数据库中添加报价单
	 */
	public void putAnswerPriceForm(AnswerPriceForm answerPriceForm) {
		Connection con = connMgr.getConnection("shipping_export");
		if (con == null) {
			System.out.println("不能获得数据库连接");
			return;
		}
		try {
			Statement stmt = con.createStatement();
			
			//添加报价单
			stmt.executeUpdate("insert into AnswerPriceForm values(" +
					answerPriceForm.getId() + ", NOW(), " +
					answerPriceForm.getShippingCost() + ", " + 
					answerPriceForm.getLandCost() + ", " +
					answerPriceForm.getDeclareCost() + ", " +
					answerPriceForm.getOrderCost() + ", " +
					answerPriceForm.getComment() + ")");
			//设置对应的询价单为已处理
			stmt.executeUpdate("update PriceQueryForm set PQF_Proceed = true where PQF_ID = '" + 
					answerPriceForm.getId() + "'");

			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 得到两港口间航行距离
	 * @param port1
	 * @param port2
	 * @return
	 */
	private double getPortDistance(String port1, String port2) {
		//随机生成一个距离作为demo
		Random distance = new Random();
		return distance.nextDouble();
	}
	private double getLandDistance(String loc1, String loc2) {
		Random distance = new Random();
		return distance.nextDouble();
	}
}
